﻿using System;
using Gumini.JulaDB.Enums;
using Gumini.JulaDB.Provider;
using Gumini.JulaDB.Test.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Gumini.JulaDB.Test
{
    /// <summary>
    /// A set of tests for SQL aggregate functions in JulaDB.
    /// </summary>
    [TestClass]
    public class AggregateFunctionTest
    {
        private static JulaDbConnection _connection;
        private static PersonTestDataCreator _peopleData;

        [ClassInitialize]
        public static void InitializeDatabase(TestContext context)
        {
            _connection = TestUtils.CreateTestDatabase();
            _peopleData = new PersonTestDataCreator(_connection);
        }

        [ClassCleanup]
        public static void CleanupDatabase()
        {
            _connection.Close();
        }

        /// <summary>
        /// A simple count() on a whole table.
        /// </summary>
        [TestMethod]
        public void CountAllRecords()
        {
            _connection.ExecuteValidScalar("select count(*) from Person", _peopleData.TotalPersonCount);
        }

        /// <summary>
        /// A simple max() on a column in the whole table.
        /// </summary>
        [TestMethod]
        public void MaxAllRecords()
        {
            _connection.ExecuteValidScalar("select max(Age) from Person", _peopleData.MaxAge);
        }

        /// <summary>
        /// A simple min() on a column in the whole table.
        /// </summary>
        [TestMethod]
        public void MinAllRecords()
        {
            _connection.ExecuteValidScalar("select min(Age) from Person", _peopleData.MinAge);
        }

        /// <summary>
        /// A simple sum() on a column in the whole table.
        /// </summary>
        [TestMethod]
        public void SumAllRecords()
        {
            _connection.ExecuteValidScalar("select sum(Age) from Person", _peopleData.SumAge);
        }

        /// <summary>
        /// A simple avg() on a column in the whole table. Uses a table alias.
        /// </summary>
        [TestMethod]
        public void AvgAllRecords()
        {
            _connection.ExecuteValidScalar("select avg(p.Age) from Person p", _peopleData.AverageAge);
        }

        /// <summary>
        /// Test for an expression consisting of aggregate functions and a WHERE condition.
        /// </summary>
        [TestMethod]
        public void AggregateExpressionAllRecords()
        {
            _connection.ExecuteValidScalar("select sum(p.Age) / count(*) from Person p where p.Age is not null", _peopleData.AverageAge);
        }

        /// <summary>
        /// Invalid nested aggregate expression. An error is expected.
        /// </summary>
        [TestMethod]
        public void NestedAggregateExpression()
        {
            _connection.ExecuteInvalidCommand("select sum(count(*)) from Person p", ErrorCode.InvalidAggregateExpression);
        }

        /// <summary>
        /// Invalid expression consisting of both aggregate and scalar function call.
        /// </summary>
        [TestMethod]
        public void MixedInvalidExpression()
        {
            _connection.ExecuteInvalidCommand("select sum(p.Age) + len(p.FirstName) from Person p", ErrorCode.InvalidAggregateExpression);
        }

        /// <summary>
        /// A GROUP BY with aggregate funtion call to get the maximum age.
        /// </summary>
        [TestMethod]
        public void MaxGroupBy()
        {
            var result = _connection.ExecuteValidSelect(
                "select LastName, max(Age) from Person group by LastName");
            Assert.AreEqual(3, result.Rows.Count);
            result.CheckRowValues(0, "Kowalski", 45M);
            result.CheckRowValues(1, "Nowak", 72.7M);
            result.CheckRowValues(2, "Kowalska", 11.5M);
        }

        /// <summary>
        /// A GROUP BY with aggregate function call to get the number of people with a particular last name.
        /// </summary>
        [TestMethod]
        public void CountGroupBy()
        {
            var result = _connection.ExecuteValidSelect(
                "select count(*), LastName from Person group by LastName");
            Assert.AreEqual(3, result.Rows.Count);
            result.CheckRowValues(0, 3M, "Kowalski");
            result.CheckRowValues(1, 3M, "Nowak");
            result.CheckRowValues(2, 1M, "Kowalska");
        }

        /// <summary>
        /// Calls to two aggreagate functions checking the expected case insensivity.
        /// </summary>
        [TestMethod]
        public void FunctionNameCaseInsensivity()
        {
            _connection.ExecuteValidScalar("select sUm(aGe), MIN(agE) from person", _peopleData.SumAge);
        }

        /// <summary>
        /// A count on an empty table should return zero.
        /// </summary>
        [TestMethod]
        public void CountEmptyTable()
        {
            _connection.ExecuteValidScalar("select count(*) from empty_table", 0);
        }

        /// <summary>
        /// Minimum of any column in an empty table should be null.
        /// </summary>
        [TestMethod]
        public void MinEmptyTable()
        {
            _connection.ExecuteValidScalar("select min(id) from empty_table", DBNull.Value);
        }

        /// <summary>
        /// Maximum of any column in an empty table should be null.
        /// </summary>
        [TestMethod]
        public void MaxEmptyTable()
        {
            _connection.ExecuteValidScalar("select max(id) from empty_table", DBNull.Value);
        }

        /// <summary>
        /// Sum of any column in an empty table should be null.
        /// </summary>
        [TestMethod]
        public void SumEmptyTable()
        {
            _connection.ExecuteValidScalar("select sum(id) from empty_table", DBNull.Value);
        }

        /// <summary>
        /// Average of any column in an empty table should be null.
        /// </summary>
        [TestMethod]
        public void AvgEmptyTable()
        {
            _connection.ExecuteValidScalar("select avg(id) from empty_table", DBNull.Value);
        }

        /// <summary>
        /// A call to undefined function should return an error.
        /// </summary>
        [TestMethod]
        public void UnsupportedFunction()
        {
            _connection.ExecuteInvalidCommand("select nonexistent_function(id) from empty_table", ErrorCode.UnsupportedFunction);
        }

        /// <summary>
        /// GROUP BY on an empty table should be valid.
        /// </summary>
        [TestMethod]
        public void MaxGroupByEmptyTable()
        {
            var result = _connection.ExecuteValidSelect("select max(id), name from empty_table group by name");
            Assert.AreEqual(0, result.Rows.Count);
        }

        /// <summary>
        /// Invalid use of GROUP BY. There is a nonaggregate SELECT expression which does not appear in GROUP BY.
        /// </summary>
        [TestMethod]
        public void InvalidGroupBy()
        {
            _connection.ExecuteInvalidCommand("select max(id), name from empty_table group by max(id)", ErrorCode.AggregateMismatch);
        }

        /// <summary>
        /// Invalid use of aggregate function in the WHERE condition.
        /// </summary>
        [TestMethod]
        public void InvalidWhereWithAggregate()
        {
            _connection.ExecuteInvalidCommand("select * from empty_table where max(id)", ErrorCode.AggregateMismatch);
        }

        /// <summary>
        /// Invalid use of aggregate function in UPDATE statement.
        /// </summary>
        [TestMethod]
        public void InvalidUpdateWithAggregate()
        {
            _connection.ExecuteInvalidCommand("update empty_table set id = max(id)", ErrorCode.AggregateMismatch);
        }

        /// <summary>
        /// Invalid use of aggregate function. SELECT uses both aggregate and scalar expressions, but there is no GROUP BY clause.
        /// </summary>
        [TestMethod]
        public void AggregateMismatchNoGroupBy()
        {
            _connection.ExecuteInvalidCommand("select max(id), name from empty_table", ErrorCode.AggregateMismatch);
        }

        /// <summary>
        /// GROUP BY a nontrivial expression (not just by a column).
        /// </summary>
        [TestMethod]
        public void GroupByFullNameLength()
        {
            var result = _connection.ExecuteValidSelect(
              @"select avg(Age)
                from Person
                group by len(FirstName || LastName)");

            Assert.AreEqual(5, result.Rows.Count);
        }

        /// <summary>
        /// GROUP BY a nontrivial expression which evaluates to bool. Two result groups are expected.
        /// </summary>
        [TestMethod]
        public void GroupByFullNameLengthByThreshold()
        {
            var result = _connection.ExecuteValidSelect(
              @"select avg(Age)
                from Person
                group by len(FirstName || LastName) > 11");

            Assert.AreEqual(2, result.Rows.Count);
        }
    }
}
